using Admin.NET.Core;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MOKA.TPM.Entity;
using MOKA.TPM.Utility.Const;
using SqlSugar;
using System.ComponentModel;

namespace MOKA.TPM.Service;

/// <summary>
/// 非采系统报表服务
/// </summary>
[ApiDescriptionSettings(TPMConst.GroupName, Order = 870)]
public class Service_PartStockReports : IDynamicApiController, ITransient
{
    private readonly SqlSugarRepository<Parts> _rep;
    private readonly UserManager _userManager;
    private IHttpContextAccessor _accessor;//获取请求上下文_userRepository
    private readonly ISqlSugarClient _db;

    /// <summary>
    /// 构造函数
    /// </summary>
    /// sadf
    /// <param name="db"></param>
    /// <param name="rep"></param>
    /// <param name="userManager"></param>
    /// <param name="accessor"></param>
    public Service_PartStockReports(ISqlSugarClient db, SqlSugarRepository<Parts> rep, UserManager userManager, IHttpContextAccessor accessor)
    {
        _db = db;
        _rep = rep;
        _userManager = userManager;
        _accessor = accessor;
    }

    /// <summary>
    /// 最近一年非采系统使用情况（按车间月份） 🧩
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [AllowAnonymous]
    [DisplayName("最近一年非采系统使用情况（按车间月份）")]
    public async Task<List<dynamic>> GetPartInOutTotalByMonthWorkshop()
    {
        var sql = @"select
	                    d.ItemName +'（' + d.ItemName2+'）'  as Workshop,
	                    YearMonth,
	                    sum(RecordCount) AS RecordCount
                        from
	                        (
			                        select m.Workshop,FORMAT(ConTime,
				                        'yyyy-MM') AS YearMonth,COUNT(*) AS RecordCount from MesTpm.dbo.PartInM m where
				                        m.ConTime >= DATEADD(YEAR, -1, GETDATE())
			                        group by m.Workshop,FORMAT(ConTime,
				                        'yyyy-MM')

			                        union

			                        select m.Workshop,FORMAT(ConTime,
				                        'yyyy-MM') AS YearMonth,COUNT(*) AS RecordCount from MesTpm.dbo.PartOutM  m where
				                        m.ConTime >= DATEADD(YEAR, -1, GETDATE())
			                        group by m.Workshop,FORMAT(ConTime,
				                        'yyyy-MM')
	                        ) m1

                        left join MesTpm.dbo.SysDictDetail  d on
	                        m1.Workshop = d.Id

                        group by
	                        d.ItemName,d.ItemName2,
	                        YearMonth
                        order by YearMonth,d.ItemName2
	     ";
        var lists = await _rep.Context.Ado.SqlQueryAsync<dynamic>(sql);

        return lists;
    }

    /// <summary>
    /// 最近一年非采系统使用情况（按用户月份） 🧩
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [AllowAnonymous]
    [DisplayName("最近一年非采系统使用情况（按用户月份）")]
    public async Task<List<dynamic>> GetPartInOutTotalByMonthUser()
    {
        var sql = @"select
	                    d.UserRemark  as [User],
	                    YearMonth,
	                    sum(RecordCount) AS RecordCount
                    from
	                    (
			                    select m.ConMan,FORMAT(ConTime,
				                    'yyyy-MM') AS YearMonth,COUNT(*) AS RecordCount from MesTpm.dbo.PartInM m where
				                    m.ConTime >= DATEADD(YEAR, -1, GETDATE())
			                    group by m.ConMan,FORMAT(ConTime,
				                    'yyyy-MM')

			                    union

			                    select m.ConMan ,FORMAT(ConTime,
				                    'yyyy-MM') AS YearMonth,COUNT(*) AS RecordCount from MesTpm.dbo.PartOutM  m where
				                    m.ConTime >= DATEADD(YEAR, -1, GETDATE())
			                    group by m.ConMan,FORMAT(ConTime,
				                    'yyyy-MM')
	                    ) m1

                    left join MESBaseDB.dbo.T_USER   d on
	                    m1.ConMan = d.UserName

                    group by
	                    d.UserRemark,
	                    YearMonth
                    order by YearMonth,d.UserRemark

	     ";
        var lists = await _rep.Context.Ado.SqlQueryAsync<dynamic>(sql);

        return lists;
    }

    /// <summary>
    /// 最近周期非采系统使用情况（按车间） 🧩
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [AllowAnonymous]
    [DisplayName("最近周期非采系统使用情况（按车间）")]
    public async Task<List<dynamic>> GetPartInOutTotalByWorkshop([FromQuery] string YearOrMonth)
    {
        if (YearOrMonth != "Year" && YearOrMonth != "Month")
        {
            throw new Exception("參數只能是Year或者Month");
        }

        var sql = @"select
                    d.ItemName +'（' + d.ItemName2+'）'  as Workshop,
                    sum(RecordCount) AS RecordCount
                    from
                        (
                                select m.Workshop,COUNT(*) AS RecordCount from MesTpm.dbo.PartInM m where
                                    m.ConTime >= DATEADD(" + YearOrMonth + @", -1, GETDATE())
                                group by m.Workshop

                                union

                                select m.Workshop,COUNT(*) AS RecordCount from MesTpm.dbo.PartOutM  m where
                                    m.ConTime >= DATEADD(" + YearOrMonth + @", -1, GETDATE())
                                group by m.Workshop
                        ) m1
                    left join MesTpm.dbo.SysDictDetail  d on
                        m1.Workshop = d.Id

                    group by
                        d.ItemName,d.ItemName2
                    order by  sum(RecordCount) desc
	     ";
        var lists = await _rep.Context.Ado.SqlQueryAsync<dynamic>(sql);

        return lists;
    }

    /// <summary>
    /// 最近周期非采系统使用情况（按用户） 🧩
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [AllowAnonymous]
    [DisplayName("最近周期非采系统使用情况（按用户）")]
    public async Task<List<dynamic>> GetPartInOutTotalByUser([FromQuery] string YearOrMonth)
    {
        if (YearOrMonth != "Year" && YearOrMonth != "Month")
        {
            throw new Exception("參數只能是Year或者Month");
        }

        var sql = @"select
                        d.UserRemark  as [User],
                        sum(RecordCount) AS RecordCount
                    from
                        (
                                select m.ConMan,COUNT(*) AS RecordCount from MesTpm.dbo.PartInM m where
                                    m.ConTime >= DATEADD(" + YearOrMonth + @", -1, GETDATE())
                                group by m.ConMan

                                union
                                select m.ConMan ,COUNT(*) AS RecordCount from MesTpm.dbo.PartOutM  m where
                                    m.ConTime >= DATEADD(" + YearOrMonth + @", -1, GETDATE())
                                group by m.ConMan
                        ) m1
                    left join MESBaseDB.dbo.T_USER   d on
	                    m1.ConMan = d.UserName
                    group by
                        d.UserRemark
                    order by sum(RecordCount) desc
	     ";
        var lists = await _rep.Context.Ado.SqlQueryAsync<dynamic>(sql);

        return lists;
    }

    /// <summary>
    /// 最近一年非采系统使用情况（按月份） 🧩
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [AllowAnonymous]
    [DisplayName("最近一年非采系统使用情况（按月份）")]
    public async Task<List<dynamic>> GetPartInOutTotalByMonth()
    {
        var sql = @"select
                    YearMonth,
                    sum(RecordCount) AS RecordCount
                    from
                        (
                                select FORMAT(ConTime,
                                    'yyyy-MM') AS YearMonth,COUNT(*) AS RecordCount from MesTpm.dbo.PartInM m where
                                    m.ConTime >= DATEADD(YEAR, -1, GETDATE())
                                group by FORMAT(ConTime,
                                    'yyyy-MM')

                                union

                                select FORMAT(ConTime,
                                    'yyyy-MM') AS YearMonth,COUNT(*) AS RecordCount from MesTpm.dbo.PartOutM  m where
                                    m.ConTime >= DATEADD(YEAR, -1, GETDATE())
                                group by FORMAT(ConTime,
                                    'yyyy-MM')
                        ) m1
                    group by
                        YearMonth
                    order by YearMonth
	     ";
        var lists = await _rep.Context.Ado.SqlQueryAsync<dynamic>(sql);

        return lists;
    }
}